const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT || 3306
};

// 数据文件路径
const DATA_FILE_PATH = path.join(__dirname, '../../..', 'shuju.md');

// 解析数据文件
const parseDataFile = () => {
  try {
    const content = fs.readFileSync(DATA_FILE_PATH, 'utf8');
    
    // 提取网站信息数据
    const webSiteMatch = content.match(/https:\/\/github\.kuaitu\.cc\/api\/web-site\?populate=\* 返回的数据如下\s*({[\s\S]*?})\s*https:/m);
    const webSiteData = webSiteMatch ? JSON.parse(webSiteMatch[1]) : null;
    
    // 提取字体数据
    const fontsMatch = content.match(/https:\/\/github\.kuaitu\.cc\/api\/fonts\?populate=\*&pagination\[pageSize\]=100 返回的数据如下\s*({[\s\S]*?})\s*https:/m);
    const fontsData = fontsMatch ? JSON.parse(fontsMatch[1]) : null;
    
    // 提取模板类型数据
    const templateTypesMatch = content.match(/https:\/\/github\.kuaitu\.cc\/api\/templ-types\?pagination\[pageSize\]=200 返回的数据如下\s*({[\s\S]*?})\s*https:/m);
    const templateTypesData = templateTypesMatch ? JSON.parse(templateTypesMatch[1]) : null;
    
    // 提取模板数据
    const templatesMatches = content.matchAll(/https:\/\/github\.kuaitu\.cc\/api\/templs\?[^\s]+ 返回的数据如下\s*({[\s\S]*?})(?=\s*https:|$)/gm);
    const templatesData = [];
    for (const match of templatesMatches) {
      try {
        const data = JSON.parse(match[1]);
        if (data.data && Array.isArray(data.data)) {
          templatesData.push(...data.data);
        }
      } catch (e) {
        console.warn('解析模板数据失败:', e.message);
      }
    }
    
    return {
      webSite: webSiteData,
      fonts: fontsData,
      templateTypes: templateTypesData,
      templates: templatesData
    };
  } catch (error) {
    console.error('读取数据文件失败:', error.message);
    throw error;
  }
};

// 主函数
const migrateData = async () => {
  let connection;
  
  try {
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');
    
    // 解析数据文件
    const data = parseDataFile();
    console.log('📄 数据文件解析完成');
    
    // 迁移网站信息
    if (data.webSite) {
      await migrateWebSite(connection, data.webSite);
    }
    
    // 迁移字体数据
    if (data.fonts) {
      await migrateFonts(connection, data.fonts);
    }
    
    // 迁移模板类型数据
    if (data.templateTypes) {
      await migrateTemplateTypes(connection, data.templateTypes);
    }
    
    // 迁移模板数据
    if (data.templates && data.templates.length > 0) {
      await migrateTemplates(connection, data.templates);
    }
    
    console.log('🎉 数据迁移完成');
  } catch (error) {
    console.error('❌ 数据迁移失败:', error.message);
    console.error(error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
};

// 迁移网站信息
const migrateWebSite = async (connection, webSiteData) => {
  console.log('📝 开始迁移网站信息...');
  
  try {
    const { data } = webSiteData;
    const { id, attributes } = data;
    const { name, url, logo } = attributes;
    
    // 获取Logo URL
    const logoUrl = logo?.data?.attributes?.url ? 
      `https://github.kuaitu.cc${logo.data.attributes.url}` : null;
    
    // 插入网站信息
    await connection.execute(`
      INSERT INTO web_site (id, site_name, logo_url, created_at, updated_at) 
      VALUES (?, ?, ?, NOW(), NOW())
      ON DUPLICATE KEY UPDATE 
      site_name = VALUES(site_name),
      logo_url = VALUES(logo_url),
      updated_at = NOW()
    `, [id, name, logoUrl]);
    
    console.log('✅ 网站信息迁移完成');
  } catch (error) {
    console.error('❌ 网站信息迁移失败:', error.message);
    throw error;
  }
};

// 迁移字体数据
const migrateFonts = async (connection, fontsApiData) => {
  console.log('📝 开始迁移字体数据...');
  
  try {
    const fontsData = fontsApiData.data;
    
    console.log(`获取到 ${fontsData.length} 个字体`);
    
    for (const font of fontsData) {
      const { id, attributes } = font;
      const { name, type, file, img } = attributes;
      
      // 获取字体文件URL
      const fontUrl = file?.data?.attributes?.url ? 
        `https://github.kuaitu.cc${file.data.attributes.url}` : null;
      
      // 获取预览图URL
      const previewUrl = img?.data?.attributes?.url ? 
        `https://github.kuaitu.cc${img.data.attributes.url}` : null;
      
      // 插入字体数据
      await connection.execute(`
        INSERT INTO fonts (id, name, font_family, font_url, preview_text, created_at, updated_at) 
        VALUES (?, ?, ?, ?, ?, NOW(), NOW())
        ON DUPLICATE KEY UPDATE 
        name = VALUES(name),
        font_family = VALUES(font_family),
        font_url = VALUES(font_url),
        preview_text = VALUES(preview_text),
        updated_at = NOW()
      `, [id, name, name, fontUrl, name]);
      
      // 如果有预览图，插入到素材表
      if (previewUrl) {
        await connection.execute(`
          INSERT INTO materials (name, type_id, url, thumbnail, file_type, tags, is_public, created_at, updated_at) 
          VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
          ON DUPLICATE KEY UPDATE 
          name = VALUES(name),
          url = VALUES(url),
          thumbnail = VALUES(thumbnail),
          updated_at = NOW()
        `, [`${name}_preview`, 1, previewUrl, previewUrl, 'image/svg+xml', JSON.stringify([type, 'font', 'preview']), true]);
      }
    }
    
    console.log('✅ 字体数据迁移完成');
  } catch (error) {
    console.error('❌ 字体数据迁移失败:', error.message);
    throw error;
  }
};

// 迁移模板类型数据
const migrateTemplateTypes = async (connection, templateTypesApiData) => {
  console.log('📝 开始迁移模板类型数据...');
  
  try {
    const templateTypesData = templateTypesApiData.data;
    
    console.log(`获取到 ${templateTypesData.length} 个模板类型`);
    
    for (const templateType of templateTypesData) {
      const { id, attributes } = templateType;
      const { name, sort } = attributes;
      
      // 插入模板类型数据
      await connection.execute(`
        INSERT INTO template_types (id, name, description, sort_order, created_at, updated_at) 
        VALUES (?, ?, ?, ?, NOW(), NOW())
        ON DUPLICATE KEY UPDATE 
        name = VALUES(name),
        description = VALUES(description),
        sort_order = VALUES(sort_order),
        updated_at = NOW()
      `, [id, name, `${name}相关模板`, sort || 0]);
    }
    
    console.log('✅ 模板类型数据迁移完成');
  } catch (error) {
    console.error('❌ 模板类型数据迁移失败:', error.message);
    throw error;
  }
};

// 迁移模板数据
const migrateTemplates = async (connection, templatesData) => {
  console.log('📝 开始迁移模板数据...');
  
  try {
    console.log(`获取到 ${templatesData.length} 个模板`);
    
    for (const template of templatesData) {
      const { id, attributes } = template;
      const { name, img } = attributes;
      
      // 获取预览图信息
      let previewUrl = null;
      let thumbnailUrl = null;
      let width = null;
      let height = null;
      
      if (img?.data?.attributes) {
        const imgAttr = img.data.attributes;
        previewUrl = `https://github.kuaitu.cc${imgAttr.url}`;
        width = imgAttr.width;
        height = imgAttr.height;
        
        // 获取缩略图
        if (imgAttr.formats?.thumbnail?.url) {
          thumbnailUrl = `https://github.kuaitu.cc${imgAttr.formats.thumbnail.url}`;
        } else if (imgAttr.formats?.small?.url) {
          thumbnailUrl = `https://github.kuaitu.cc${imgAttr.formats.small.url}`;
        } else {
          thumbnailUrl = previewUrl;
        }
      }
      
      // 插入模板数据 (type_id=20 对应小红书类型)
      await connection.execute(`
        INSERT INTO templates (id, name, type_id, preview, thumbnail, width, height, json_data, tags, is_public, download_count, created_at, updated_at) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
        ON DUPLICATE KEY UPDATE 
        name = VALUES(name),
        type_id = VALUES(type_id),
        preview = VALUES(preview),
        thumbnail = VALUES(thumbnail),
        width = VALUES(width),
        height = VALUES(height),
        tags = VALUES(tags),
        updated_at = NOW()
      `, [id, name, 20, previewUrl, thumbnailUrl, width, height, null, JSON.stringify(['小红书', '社交媒体']), true, 0]);
    }
    
    console.log('✅ 模板数据迁移完成');
  } catch (error) {
    console.error('❌ 模板数据迁移失败:', error.message);
    throw error;
  }
};

// 运行迁移
if (require.main === module) {
  migrateData();
}

module.exports = { migrateData };